<?php

class Export{
	
	private $db;
	
	function __construct($db){
		$this->db = $db;
	}
	
	private function getExportEntries($year = null, $month = null, $project = null){
		// Initialization of WHERE statement
		$where = '';
		
		// Year condition is added
		if($year != null){
			if(strlen($where) != 0)
				$where .= " AND ";
			
			$where .= "strftime('%Y', Arbeitszeit.von) = $year";
		}
		
		// Month condition is added and AND is appended if neccessary.
		if($month != null){
			if(strlen($where) != 0)
			$where .= " AND ";
				
			$where .= "strftime('%m', Arbeitszeit.von) = $month";
		}
		
		// Project condition is verified and added.
		if($project != null){
			if(strlen($where) != 0)
			$where .= " AND ";
				
			$where .= "Projekt.pk_nr = $project";
		}
		
		if(strlen($where) != 0){
			$where = "WHERE " . $where;
		}
		
		// Resource is generated according to given parameters.
		$entries = $this->db->query("SELECT * FROM Arbeitet
								INNER JOIN Personal ON Personal.pk_persnr = Arbeitet.fk_personal 
								INNER JOIN Arbeitszeit ON Arbeitszeit.pk_id = Arbeitet.fk_arbeitszeit
								INNER JOIN Projekt ON Projekt.pk_nr = Arbeitet.fk_projekt
								" . $where . " 
								ORDER BY Personal.pk_persnr, Projekt.pk_nr, Arbeitszeit.von ASC");
		
		return $entries;
	}
	
	/*
	 * Exports the calendar as iCal file.
	 * If the project given is null, all projects will be exported.
	 */
	function exportICal($project = null){
		
		$where = '';
		
		if ( $project != null ) {
			$where = " WHERE Projekt.pk_nr = $project ";
		}
		
		// Resource is generated according to given parameters.
		$entries = $this->db->query("SELECT Termin.pk_id, Termin.ort, Termin.text, 
				strftime('%Y%m%d',Termin.Datum) AS Datum, strftime('%H%M%S',Termin.Uhrzeit) AS Uhrzeit, Projekt.name FROM Termin
				INNER JOIN Projekt ON Termin.fk_projekt = Projekt.pk_nr
				$where
				ORDER BY Termin.datum ASC, Termin.Uhrzeit ASC");
		
		$nl = "\n";
		
		$result =  "BEGIN:VCALENDAR".$nl;
		$result.=  "VERSION:2.0".$nl;
		$result.=  "PRODID:http://code.google.com/p/fhj-ss2012-php-timemanagement".$nl;
		$result.=  "METHOD:PUBLISH".$nl;
		
		foreach( $entries as $entry ) {
			$date = $entry['Datum'] . "T" .  $entry['Uhrzeit'] . "Z";
			
			$result .= "BEGIN:VEVENT".$nl;
			$result .= "UID:".$entry['Termin.pk_id']."@fh-joanneum.at".$nl;
			$result .= "LOCATION:".$entry['Termin.ort'].$nl;
			$result .= "SUMMARY: Projekt: ".$entry['Projekt.name']." | ".$entry['Termin.text'].$nl;
			$result .= "DESCRIPTION:".$entry['Termin.text'].$nl;
			$result .= "CLASS:PUBLIC".$nl;
			$result .= "DTSTART:".$date.$nl;
			$result .= "DTEND:".$date.$nl;
			$result .= "END:VEVENT".$nl;
		}
		
		$result .= "END:VCALENDAR";
		
		header("Content-Type: text/calendar");
		header("Content-Disposition: attachment; filename=\"termine.ical\"");
		
		echo $result;
	}
	
	/*
	 * Exports the calendar as CSV file.
	 * If no year is given, all entries of all years will be exported.
	 * If month is null, only the given years project entires will be exported.
	 * If the project is null, all entries of all projects will be exported.
	 */
	function exportCSV($year = null, $month = null, $project = null){
		
		$entries = $this->getExportEntries($year, $month, $project);
		
		$result = "Arbeitet.pk_id;" . 
				"Arbeitet.fk_personal;" . 
				"Arbeitet.fk_arbeitszeit;" . 
				"Arbeitet.fk_projekt;" . 
				"Personal.pk_persnr;" . 
				"Personal.name;" . 
				"Personal.gebdatum;" . 
				"Personal.rolle;" . 
				"Arbeitszeit.pk_id;" . 
				"Arbeitszeit.von;" . 
				"Arbeitszeit.bis;" . 
				"Projekt.pk_nr;" . 
				"Projekt.name;" . 
				"Projekt.deadline;" . 
				"Projekt.budget\n";
		
		foreach($entries as $entry){

			$result .= $entry['Arbeitet.pk_id'] . ";" . 
				$entry['Arbeitet.fk_personal'] . ";" . 
				$entry['Arbeitet.fk_arbeitszeit'] . ";" . 
				$entry['Arbeitet.fk_projekt'] . ";" . 
				$entry['Personal.pk_persnr'] . ";" . 
				$entry['Personal.name'] . ";" . 
				$entry['Personal.gebdatum'] . ";" . 
				$entry['Personal.rolle'] . ";" . 
				$entry['Arbeitszeit.pk_id'] . ";" . 
				$entry['Arbeitszeit.von'] . ";" . 
				$entry['Arbeitszeit.bis'] . ";" . 
				$entry['Projekt.pk_nr'] . ";" . 
				$entry['Projekt.name'] . ";" . 
				$entry['Projekt.deadline'] . ";" . 
				$entry['Projekt.budget'] . "\n";
		}
		
		header("Content-Type: text/comma-separated-values");
		header("Content-Disposition: attachment; filename=\"export.csv\"");
		echo $result;
	}
	
	/*
	 * Exports the calendar as XML file.
	 */
	function exportXML($year = null, $month = null, $project = null){
		
		$entries = $this->getExportEntries($year, $month, $project);
		
		$document = new DOMDocument("1.0", "utf-8");
		$root = $document->createElement("Calendar");
		$root = $document->appendChild($root);
		
		foreach($entries as $entry){
			$newentry = $document->createElement("Entry");
			$newentry = $root->appendChild($newentry);
			
			foreach($entry as $field => $key){
				$nextfield = $document->createElement($field);
				$nextfield = $newentry->appendChild($nextfield);
				
				$nextvalue = $document->createTextNode($key);
				$nextvalue = $nextfield->appendChild($nextvalue);
			}
			
			
		}
		
		$xml_string = $document->saveXML();
		echo $xml_string;
		
		header("Content-Type: application/xml");
		header("Content-Disposition: attachment; filename=\"export.xml\"");
		
	}
	
}